-- Convert from dbo.kompy to dbo.Units - start --
ALTER TABLE [dbo].[kompy] ALTER COLUMN [nomerPC] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [CPU1] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [CPU2] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [CPU3] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [CPU4] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [Mb] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [RAM_1] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [RAM_2] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [RAM_3] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [RAM_4] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [HDD_Name_1] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [HDD_Name_2] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [HDD_Name_3] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [HDD_Name_4] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [SVGA_NAME] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [SVGA2_NAME] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [SOUND_NAME] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [CD_NAME] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [CDRW_NAME] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [DVD_NAME] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [NET_NAME_1] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [NET_NAME_2] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [FDD_NAME] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [MODEM_NAME] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [KEYBOARD_NAME] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [MOUSE_NAME] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [USB_NAME] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [PCI_NAME] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [MONITOR_NAME] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [MONITOR_NAME2] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [AS_NAME] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [IBP_NAME] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [FILTR_NAME] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [PRINTER_NAME_1] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [PRINTER_NAME_2] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [PRINTER_NAME_3] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [PRINTER_NAME_4] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [SCANER_NAME] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [INV_NO_SYSTEM] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [INV_NO_PRINTER] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [INV_NO_MODEM] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [INV_NO_SCANER] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [INV_NO_MONITOR] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [INV_NO_IBP] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [OS] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [OTvetstvennyj] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [Ser_N_SIS] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [BARKODE] NVARCHAR(MAX)
ALTER TABLE [dbo].[kompy] ALTER COLUMN [PATH] NVARCHAR(MAX)
-- Convert from dbo.kompy to dbo.Units - end --
GO

TRUNCATE TABLE [dbo].[Units]

INSERT INTO [dbo].[Units]
	(INV_NO, SN, SPR_ID, SPR_Type, FILIAL_ID, OTDEL_ID, KAB_ID)

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	k.HDD_SN_1 AS SN,
	spr.id AS SPR_ID,
	'HDD' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_HDD spr ON k.HDD_Name_1 = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'PC'

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	k.HDD_SN_2 AS SN,
	spr.id AS SPR_ID,
	'HDD' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_HDD spr ON k.HDD_Name_2 = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'PC'

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	k.HDD_SN_3 AS SN,
	spr.id AS SPR_ID,
	'HDD' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_HDD spr ON k.HDD_Name_3 = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'PC'

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	k.HDD_SN_4 AS SN,
	spr.id AS SPR_ID,
	'HDD' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_HDD spr ON k.HDD_Name_4 = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'PC'

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	NULL AS SN,
	spr.id AS SPR_ID,
	'CPU' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_CPU spr ON k.CPU1 = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'PC'

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	NULL AS SN,
	spr.id AS SPR_ID,
	'CPU' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_CPU spr ON k.CPU2 = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'PC'

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	NULL AS SN,
	spr.id AS SPR_ID,
	'CPU' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_CPU spr ON k.CPU3 = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'PC'

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	NULL AS SN,
	spr.id AS SPR_ID,
	'CPU' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_CPU spr ON k.CPU4 = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'PC'

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	k.CASE_SN AS SN,
	spr.id AS SPR_ID,
	'CASE' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_CASE spr ON k.CASE_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	k.SN_BLOCK AS SN,
	spr.id AS SPR_ID,
	'POWER' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_BP spr ON k.BLOCK = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	k.CDRW_SN AS SN,
	spr.id AS SPR_ID,
	'OPTICAL' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_OPTICAL spr ON k.CDRW_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'PC'

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	k.CD_SN AS SN,
	spr.id AS SPR_ID,
	'OPTICAL' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_OPTICAL spr ON k.CD_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'PC'

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	k.DVD_SN AS SN,
	spr.id AS SPR_ID,
	'OPTICAL' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_OPTICAL spr ON k.DVD_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'PC'

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	k.CREADER_SN AS SN,
	spr.id AS SPR_ID,
	'CREADER' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_CREADER spr ON k.CREADER_SN = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	k.FDD_SN AS SN,
	spr.id AS SPR_ID,
	'FDD' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_FDD spr ON k.FDD_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name

UNION

SELECT
	k.INV_NO_IBP AS INV_NO,
	k.IBP_SN AS SN,
	spr.id AS SPR_ID,
	'IBP' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_IBP spr ON k.IBP_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	k.KEYBOARD_SN AS SN,
	spr.id AS SPR_ID,
	'KEYBOARD' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_KEYBOARD spr ON k.KEYBOARD_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name

UNION

SELECT
	k.INV_NO_MODEM AS INV_NO,
	k.MODEM_SN AS SN,
	spr.id AS SPR_ID,
	'MODEM' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_MODEM spr ON k.MODEM_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name

UNION

SELECT
	k.INV_NO_MONITOR AS INV_NO,
	k.MONITOR_SN AS SN,
	spr.id AS SPR_ID,
	'MONITOR' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_MONITOR spr ON k.MONITOR_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name

UNION

SELECT
	k.INV_NO_MONITOR AS INV_NO,
	k.MONITOR_SN2 AS SN,
	spr.id AS SPR_ID,
	'MONITOR' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_MONITOR spr ON k.MONITOR_NAME2 = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	k.MOUSE_SN AS SN,
	spr.id AS SPR_ID,
	'MOUSE' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_MOUSE spr ON k.MOUSE_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	NULL AS SN,
	spr.id AS SPR_ID,
	'MB' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_MB spr ON k.Mb = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	k.PCI_SN AS SN,
	spr.id AS SPR_ID,
	'PCI' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_PCI spr ON k.PCI_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name

UNION

SELECT
	k.INV_NO_PRINTER AS INV_NO,
	k.PRINTER_SN_1 AS SN,
	spr.id AS SPR_ID,
	'PRINTER' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_PRINTER spr ON k.PRINTER_NAME_1 = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'PC'

UNION

SELECT
	k.INV_NO_PRINTER AS INV_NO,
	k.PRINTER_SN_2 AS SN,
	spr.id AS SPR_ID,
	'PRINTER' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_PRINTER spr ON k.PRINTER_NAME_2 = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'PC'

UNION

SELECT
	k.INV_NO_PRINTER AS INV_NO,
	k.PRINTER_SN_3 AS SN,
	spr.id AS SPR_ID,
	'PRINTER' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_PRINTER spr ON k.PRINTER_NAME_3 = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'PC'

UNION

SELECT
	k.INV_NO_PRINTER AS INV_NO,
	k.PRINTER_SN_4 AS SN,
	spr.id AS SPR_ID,
	'PRINTER' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_PRINTER spr ON k.PRINTER_NAME_4 = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'PC'

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	k.RAM_SN_1 AS SN,
	spr.id AS SPR_ID,
	'RAM' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_RAM spr ON k.RAM_1 = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'PC'

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	k.RAM_SN_2 AS SN,
	spr.id AS SPR_ID,
	'RAM' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_RAM spr ON k.RAM_2 = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'PC'

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	k.RAM_SN_3 AS SN,
	spr.id AS SPR_ID,
	'RAM' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_RAM spr ON k.RAM_3 = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'PC'

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	k.RAM_SN_4 AS SN,
	spr.id AS SPR_ID,
	'RAM' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_RAM spr ON k.RAM_4 = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'PC'

UNION

SELECT
	k.INV_NO_SCANER AS INV_NO,
	k.SCANER_SN AS SN,
	spr.id AS SPR_ID,
	'SCANER' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_SCANER spr ON k.SCANER_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name

UNION

SELECT
	k.INV_NO_PRINTER AS INV_NO,
	k.PRINTER_SN_1 AS SN,
	spr.id AS SPR_ID,
	'SCANER' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_SCANER spr ON k.NET_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'SCANER'

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	k.SOUND_SN AS SN,
	spr.id AS SPR_ID,
	'SOUND' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_SOUND spr ON k.SOUND_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	k.SVGA_SN AS SN,
	spr.id AS SPR_ID,
	'SVGA' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_SVGA spr ON k.SVGA_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	k.SVGA2_SN AS SN,
	spr.id AS SPR_ID,
	'SVGA' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_SVGA spr ON k.SVGA2_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	k.USB_SN AS SN,
	spr.id AS SPR_ID,
	'USB' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_USB spr ON k.USB_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	k.AS_SN AS SN,
	spr.id AS SPR_ID,
	'ASISTEM' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_ASISTEM spr ON k.AS_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name

UNION

SELECT
	k.INV_NO_PRINTER AS INV_NO,
	k.PRINTER_SN_1 AS SN,
	spr.id AS SPR_ID,
	'ZIP' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_ZIP spr ON k.NET_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'ZIP'

UNION

SELECT
	k.INV_NO_PRINTER AS INV_NO,
	k.PRINTER_SN_1 AS SN,
	spr.id AS SPR_ID,
	'KOPIR' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_KOPIR spr ON k.NET_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'KOpir'

UNION

SELECT
	k.INV_NO_PRINTER AS INV_NO,
	k.PRINTER_SN_1 AS SN,
	spr.id AS SPR_ID,
	'MFU' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_MFU spr ON k.NET_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'MFU'

UNION

SELECT
	k.INV_NO_SYSTEM AS INV_NO,
	k.FILTR_SN AS SN,
	spr.id AS SPR_ID,
	'FILTER' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_FS spr ON k.FILTR_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name

UNION

SELECT
	k.INV_NO_PRINTER AS INV_NO,
	k.PRINTER_SN_1 AS SN,
	spr.id AS SPR_ID,
	'NET' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_DEV_NET spr ON k.NET_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'NET'

UNION

SELECT
	k.INV_NO_PRINTER AS INV_NO,
	k.PRINTER_SN_1 AS SN,
	spr.id AS SPR_ID,
	'PHONE' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN spr_phone spr ON k.NET_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'PHONE'

UNION

SELECT
	k.INV_NO_PRINTER AS INV_NO,
	k.PRINTER_SN_1 AS SN,
	spr.id AS SPR_ID,
	'FAX' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN spr_fax spr ON k.NET_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'FAX'

UNION

SELECT
	k.INV_NO_PRINTER AS INV_NO,
	k.PRINTER_SN_1 AS SN,
	spr.id AS SPR_ID,
	'PHOTO' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN spr_photo spr ON k.NET_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'PHOTO'

UNION

SELECT
	k.INV_NO_PRINTER AS INV_NO,
	k.PRINTER_SN_1 AS SN,
	spr.id AS SPR_ID,
	'OTHER' AS SPR_Type,
	f.ID AS FILIAL_ID,
	o.Id AS OTDEL_ID,
	k1.ID AS KAB_ID
FROM [dbo].[kompy] k
INNER JOIN SPR_OTH_DEV spr ON k.NET_NAME = spr.Name
INNER JOIN SPR_FILIAL f ON k.FILIAL = f.FILIAL
INNER JOIN SPR_OTD_FILIAL o ON k.MESTO = o.N_Otd AND k.FILIAL = o.Filial
INNER JOIN SPR_KAB k1 ON k.kabn = k1.Name
WHERE k.TipTehn = 'OT'

GO

-- Convert from dbo.kompy to dbo.Units - start --
ALTER TABLE [dbo].[kompy] ALTER COLUMN [nomerPC] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [CPU1] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [CPU2] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [CPU3] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [CPU4] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [Mb] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [RAM_1] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [RAM_2] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [RAM_3] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [RAM_4] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [HDD_Name_1] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [HDD_Name_2] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [HDD_Name_3] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [HDD_Name_4] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [SVGA_NAME] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [SVGA2_NAME] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [SOUND_NAME] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [CD_NAME] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [CDRW_NAME] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [DVD_NAME] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [NET_NAME_1] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [NET_NAME_2] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [FDD_NAME] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [MODEM_NAME] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [KEYBOARD_NAME] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [MOUSE_NAME] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [USB_NAME] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [PCI_NAME] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [MONITOR_NAME] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [MONITOR_NAME2] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [AS_NAME] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [IBP_NAME] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [FILTR_NAME] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [PRINTER_NAME_1] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [PRINTER_NAME_2] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [PRINTER_NAME_3] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [PRINTER_NAME_4] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [SCANER_NAME] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [INV_NO_SYSTEM] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [INV_NO_PRINTER] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [INV_NO_MODEM] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [INV_NO_SCANER] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [INV_NO_MONITOR] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [INV_NO_IBP] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [OS] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [OTvetstvennyj] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [Ser_N_SIS] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [BARKODE] NTEXT
ALTER TABLE [dbo].[kompy] ALTER COLUMN [PATH] NTEXT
-- Convert from dbo.kompy to dbo.Units - end --
GO
